package com.sinobpo.rnd2.ehr.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.sinobpo.rnd2.ehr.dao.ClinicSatDao;
import com.sinobpo.rnd2.ehr.model.ClinicSat;
import com.sinobpo.rnd2.ehr.util.CommonUtil;
import com.sinobpo.rnd2.ehr.util.TimeType;

/**
 * 门诊数据接口实现类
 * 
 * @author yinghui.zhang
 * @CreateDate 2012-7-11
 * @file ClinicSatDaoImpl.java
 * @package com.sinobpo.rnd2.ehr.dao.impl
 * @version 1.0
 * @Email uuhui@163.com
 */
@Component("clinicSatDaoImpl")
@Transactional
public class ClinicSatDaoImpl implements ClinicSatDao {
	@Resource
	private SimpleJdbcTemplate simpleJdbcTemplate;

	@Override
	@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
	public List<ClinicSat> getSum(TimeType timeType, Object... conds) {
		String groupByColumn = null;
		String sql = null;
		switch (timeType) {
		case DAY:
			sql = "SELECT SHANGBAORQ,SUM(MENZHENRC)  MENZHENRC ,SUM(JIZHENRC) JIZHENRC FROM TBL_MENZHENLTJ ";
			groupByColumn = "SHANGBAORQ";
			sql += "WHERE  SHANGBAORQ BETWEEN TO_DATE(?,'YYYY-MM-DD') AND TO_DATE(?,'YYYY-MM-DD') ";
			break;
		case MONTH:
			groupByColumn = "TO_CHAR(SHANGBAORQ,'MM')";
			sql = "SELECT TO_CHAR(SHANGBAORQ,'MM') SHANGBAORQ,SUM(MENZHENRC)  MENZHENRC ,SUM(JIZHENRC) JIZHENRC FROM TBL_MENZHENLTJ ";
			sql += "WHERE TO_CHAR(SHANGBAORQ,'MM')>=? and TO_CHAR(SHANGBAORQ,'MM')<=?";
			break;
		case YEAR:
			groupByColumn = "TO_CHAR(SHANGBAORQ,'YYYY')";
			sql = "SELECT TO_CHAR(SHANGBAORQ,'YYYY') SHANGBAORQ,SUM(MENZHENRC)  MENZHENRC ,SUM(JIZHENRC) JIZHENRC FROM TBL_MENZHENLTJ ";
			sql += "WHERE TO_CHAR(SHANGBAORQ,'YYYY')>=? and TO_CHAR(SHANGBAORQ,'YYYY')<=?";
			break;
		}
		List<String> list = new ArrayList<String>();
		list.add((String) conds[1]);// /添加开始时间
		list.add((String) conds[2]);// /添加结束时间
		if (conds[0] != null && !"".equals(conds[0])) {
			sql += " AND JIGOUDM=?";
			list.add((String) conds[0]);
		}
		sql += " GROUP BY " + groupByColumn + " ORDER BY SHANGBAORQ";
		return simpleJdbcTemplate.query(sql,
				new ParameterizedRowMapper<ClinicSat>() {
					public ClinicSat mapRow(java.sql.ResultSet rs, int rowNum)
							throws java.sql.SQLException {
						ClinicSat clinicSate = new ClinicSat();
						clinicSate.setRepDate(rs.getString(1));
						clinicSate.setOpsNum(rs.getInt(2));
						clinicSate.setEmergencyNum(rs.getInt(3));
						return clinicSate;
					}
				}, list.toArray());
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
	public Object getSumByType(TimeType timeType, Object... conds) {
		String sql = "SELECT  SUM(MENZHENRC)  MENZHENRC ,SUM(JIZHENRC) JIZHENRC FROM TBL_MENZHENLTJ ";
		switch (timeType) {
		case DAY:
			sql += "WHERE  SHANGBAORQ BETWEEN TO_DATE(?,'YYYY-MM-DD') AND TO_DATE(?,'YYYY-MM-DD') ";
			break;
		case MONTH:
			sql += "WHERE TO_CHAR(SHANGBAORQ,'MM')>=? and TO_CHAR(SHANGBAORQ,'MM')<=?";
			break;
		case YEAR:
			sql += "WHERE TO_CHAR(SHANGBAORQ,'YYYY')>=? and TO_CHAR(SHANGBAORQ,'YYYY')<=?";
			break;
		}
		List<String> list = new ArrayList<String>();
		list.add((String) conds[1]);
		list.add((String) conds[2]);
		if (conds[0] != null && !"".equals(conds[0])) {
			sql += " AND JIGOUDM=?";
			list.add((String) conds[0]);
		}
		return simpleJdbcTemplate.queryForObject(sql,
				new ParameterizedRowMapper() {
					public Object mapRow(java.sql.ResultSet rs, int rowNum)
							throws java.sql.SQLException {
						return new Object[] { rs.getString(1), rs.getString(2) };
					}
				}, list.toArray());
	}

	@Override
	@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
	public List<ClinicSat> getList(int page, int rows, TimeType timeType,
			Object... conds) {
		String sql = "SELECT * FROM (SELECT T.*, ROWNUM RN FROM ";
		List<String> list = new ArrayList<String>();
		String groupByColumn = null;
		switch (timeType) {
		case DAY: {// 综合统计
			sql += "(SELECT JIGOUDM,SHANGBAORQ,MENZHENRC,JIZHENRC FROM TBL_MENZHENLTJ WHERE 1=1  ";
			sql += " AND SHANGBAORQ BETWEEN TO_DATE(?,'YYYY-MM-DD') AND TO_DATE(?,'YYYY-MM-DD') ";
			break;
		}
		case MONTH: {// 按月度统计
			sql += "(SELECT JIGOUDM,TO_CHAR(SHANGBAORQ,'MM') SHANGBAORQ,SUM(MENZHENRC) MENZHENRC,SUM(JIZHENRC) JIZHENRC FROM TBL_MENZHENLTJ WHERE 1=1  ";
			sql += " AND TO_CHAR(SHANGBAORQ,'MM')>=? AND TO_CHAR(SHANGBAORQ,'MM')<=? ";
			groupByColumn = " GROUP BY JIGOUDM,TO_CHAR(SHANGBAORQ,'MM')";
			break;
		}
		case YEAR: {// 按年度统计
			sql += "(SELECT JIGOUDM,TO_CHAR(SHANGBAORQ,'YYYY') SHANGBAORQ,SUM(MENZHENRC) MENZHENRC,SUM(JIZHENRC) JIZHENRC FROM TBL_MENZHENLTJ WHERE 1=1  ";
			sql += " AND TO_CHAR(SHANGBAORQ,'YYYY')>=? AND TO_CHAR(SHANGBAORQ,'YYYY')<=? ";
			groupByColumn = "GROUP BY JIGOUDM,TO_CHAR(SHANGBAORQ,'YYYY')";
			break;
		}
		}
		list.add((String) conds[1]);
		list.add((String) conds[2]);
		if (conds[0] != null && !conds[0].equals("")) {// 医疗机构代码不为空时的查询条件
			sql += " AND  JIGOUDM=? ";
			list.add((String) conds[0]);
		}
		if (groupByColumn != null)
			sql += groupByColumn + "  ORDER BY SHANGBAORQ ) T ";
		else
			sql += "  ORDER BY SHANGBAORQ ) T ";
		sql += "WHERE ROWNUM <= " + page * rows;
		sql += " ) WHERE RN >" + (page - 1) * rows;
		return simpleJdbcTemplate.query(sql,
				new ParameterizedRowMapper<ClinicSat>() {
					Map<String, String> map = CommonUtil.parseXml(false);

					public ClinicSat mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						ClinicSat clinicSat = new ClinicSat();
						clinicSat.setOrgCode(rs.getString(1));
						clinicSat.setOrgName(map.get(rs.getString(1)));
						clinicSat.setRepDate(rs.getString(2));
						clinicSat.setOpsNum(rs.getInt(3));
						clinicSat.setEmergencyNum(rs.getInt(4));
						clinicSat.setTotalNum(rs.getInt(3) + rs.getInt(4));
						return clinicSat;
					}
				}, list.toArray());
	}

	@Override
	@Transactional(propagation = Propagation.NOT_SUPPORTED, readOnly = true)
	public int getCount(TimeType timeType, Object... conds) {
		String sql = "SELECT COUNT(0) C FROM TBL_MENZHENLTJ WHERE 1=1 ";
		List<String> list = new ArrayList<String>();
		if (conds[0] != null && !conds[0].equals("")) {// 医疗机构代码不为空时的查询条件
			sql += " AND  JIGOUDM=? ";
			list.add((String) conds[0]);
		}
		switch (timeType) {
		case DAY: {// 综合统计
			sql += " AND SHANGBAORQ BETWEEN TO_DATE(?,'YYYY-MM-DD') AND TO_DATE(?,'YYYY-MM-DD') ";
			break;
		}
		case MONTH: {// 按月度统计
			sql += " AND TO_CHAR(SHANGBAORQ,'MM')>=? AND TO_CHAR(SHANGBAORQ,'MM')<=? ";
			sql += " GROUP BY JIGOUDM,TO_CHAR(SHANGBAORQ,'MM')";
			break;
		}
		case YEAR: {// 按年度统计
			sql += " AND TO_CHAR(SHANGBAORQ,'YYYY')>=? AND TO_CHAR(SHANGBAORQ,'YYYY')<=? ";
			sql += " GROUP BY JIGOUDM,TO_CHAR(SHANGBAORQ,'YYYY')";
			break;
		}
		}
		list.add((String) conds[1]);
		list.add((String) conds[2]);
		if (timeType.equals(TimeType.DAY))
			return simpleJdbcTemplate.queryForInt(sql, list.toArray());
		else
			return simpleJdbcTemplate.queryForList(sql, list.toArray()).size();
	}
}
